﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace ECCSystem.DataAccess
{ 
    /// <summary>
    ///  DbObject is the class from which all classes in the Data Services	Tier
    ///inherit. The core functionality of establishing a connection with the
    ///database and executing simple stored procedures is also provided by
    ///this base class.
    /// </summary>
    public abstract class DBObject
    {
        // Methods
        //A parameterized constructor, it allows us to take a connection string
        //as a constructor argument, automatically instantiating a new connection
        public DBObject()
        {
        }
        //Builds a SqlCommand designed to return a DataSet or DataReader
        private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] objParameters)
        {
            SqlCommand objCmd = this.BuildQueryCommand(storedProcName, objParameters);
            SqlParameter objParameter1 = new SqlParameter();
            SqlParameter objParameter2 = objParameter1;
            objParameter2.ParameterName = "ReturnValue";
            objParameter2.DbType = DbType.Double;
            objParameter2.Size = 4;
            objParameter2.Direction = ParameterDirection.ReturnValue;
            objParameter2.IsNullable = false;
            objParameter2.Precision = 0;
            objParameter2.Scale = 0;
            objParameter2.SourceColumn = string.Empty;
            objParameter2.SourceVersion = DataRowVersion.Default;
            objParameter2.Value = null;
            objParameter2 = null;
            objCmd.Parameters.Add(objParameter1);
            return objCmd;
        }
        //Builds a SqlCommand designed to obtain a return value of executing the stored procedure
        private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] objParameters)
        {
            SqlCommand objCmd = new SqlCommand(storedProcName,BizRule.ClsCommon.gObjConn);
            objCmd.CommandType = CommandType.StoredProcedure;
			objCmd.CommandTimeout = 60;
            foreach (SqlParameter objParameter in objParameters)
            {
                objCmd.Parameters.Add(objParameter);
            }
            return objCmd;
        }
        //Runs a stored procedure, can only be called by those classes deriving from the base.
        //It returns a SqlDataReader containing the result of executing the stored procedure.
        protected DataSet RunProcedure(string storedProcName, string tableName)
        {
            SqlCommand objCmd = new SqlCommand(storedProcName, BizRule.ClsCommon.gObjConn);
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandTimeout = 60;

            DataSet objDS = new DataSet();
            SqlDataAdapter objAdpt = new SqlDataAdapter();
            objAdpt.SelectCommand = objCmd;
            objAdpt.Fill(objDS, tableName);
            return objDS;
        }

        protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] objParameters)
        {
            SqlCommand objCmd = this.BuildQueryCommand(storedProcName, objParameters);
            objCmd.CommandType = CommandType.StoredProcedure;
            return objCmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        //Runs a stored procedure, can only be called by those classes deriving from the base
        //It returns an integer indicating the return value of the stored procedure, 
        //and also returns a value of RowsAffected aspect of the stored procedure that is returned by 
        //the ExecuteNonQuery Method.
        protected int RunProcedure(string storedProcName, IDataParameter[] objParameters, ref int rowsAffected)
        {
            SqlCommand objCmd = this.BuildIntCommand(storedProcName, objParameters);
            rowsAffected = objCmd.ExecuteNonQuery();
            int intNum = (int)objCmd.Parameters["ReturnValue"].Value;
            return intNum;
        }
        //Returns a DataSet by running a stored procedure and placing the results into the given table name
        protected DataSet RunProcedure(string storedProcName, IDataParameter[] objParameters, string tableName)
        {
            DataSet objDS = new DataSet();
            SqlDataAdapter objAdpt = new SqlDataAdapter();
            objAdpt.SelectCommand = this.BuildQueryCommand(storedProcName, objParameters);
            objAdpt.Fill(objDS, tableName);
            return objDS;
        }
        //Takes an existing DataSet and fills the given table name with the results of the stored procedure
        protected void RunProcedure(string storedProcName, IDataParameter[] objParameters, DataSet objDataSet, string tableName)
        {
            SqlDataAdapter objAdpt = new SqlDataAdapter();
            objAdpt.SelectCommand = this.BuildQueryCommand(storedProcName, objParameters);
            objAdpt.Fill(objDataSet, tableName);
        }


		private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] objParameters, SqlCommand objCmd)
		{
			this.BuildQueryCommand(storedProcName, objParameters, objCmd);
			SqlParameter objParameter1 = new SqlParameter();
			SqlParameter objParameter2 = objParameter1;
			objParameter2.ParameterName = "ReturnValue";
			objParameter2.DbType = DbType.Double;
			objParameter2.Size = 4;
			objParameter2.Direction = ParameterDirection.ReturnValue;
			objParameter2.IsNullable = false;
			objParameter2.Precision = 0;
			objParameter2.Scale = 0;
			objParameter2.SourceColumn = string.Empty;
			objParameter2.SourceVersion = DataRowVersion.Default;
			objParameter2.Value = null;
			objParameter2 = null;
			objCmd.Parameters.Add(objParameter1);
			return objCmd;
		}
		//Builds a SqlCommand designed to obtain a return value of executing the stored procedure
		private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] objParameters, SqlCommand objCmd)
		{
			//objCmd = new SqlCommand(storedProcName, BizRule.ClsCommon.gObjConn);
			objCmd.CommandType = CommandType.StoredProcedure;
			objCmd.CommandTimeout = 60;
			foreach (SqlParameter objParameter in objParameters)
			{
				objCmd.Parameters.Add(objParameter);
			}
			return objCmd;
		}


		protected int RunProcedure(string storedProcName, IDataParameter[] objParameters, ref int rowsAffected, SqlTransaction objTran, SqlCommand objCmd)
		{

			if (objCmd.Parameters.Count != 0)
			{
				int intCount = objCmd.Parameters.Count;
				while (objCmd.Parameters.Count != 0)
				{
					objCmd.Parameters.RemoveAt(0);
				}
				objCmd = this.BuildIntCommand(storedProcName, objParameters, objCmd);
				objCmd.Transaction = objTran;
			}
			else
			{
				objCmd = this.BuildIntCommand(storedProcName, objParameters, objCmd);
				objCmd.Transaction = objTran;
			}

			rowsAffected = objCmd.ExecuteNonQuery();
			int intNum = (int)objCmd.Parameters["ReturnValue"].Value;
			return intNum;
		}

		protected DataSet RunProcedure(string storedProcName, IDataParameter[] objParameters, string tableName, SqlTransaction objTran, SqlCommand objCmd)
		{
			DataSet objDS = new DataSet();
			SqlDataAdapter objAdpt = new SqlDataAdapter();
			objCmd.Transaction = objTran;
			objAdpt.SelectCommand = this.BuildQueryCommand(storedProcName, objParameters, objCmd);
			objAdpt.Fill(objDS, tableName);
			return objDS;
		}

    }

}

